# load data
df1 <- read_excel("dat/auxiliary/tenant_total.xlsx", sheet = 3)
df2 <- read_excel("dat/auxiliary/tenant_above.xlsx", sheet = 3)
df3 <- read_excel("dat/auxiliary/tenant_below.xlsx", sheet = 3)

df1 <- df1[11:52, 1:2]
names(df1) <- c("geo", "total")

df2 <- df2[11:52, 2]
names(df2) <- c("above")

df3 <- df3[11:52, 2]
names(df3) <- c("below")

df <- cbind(df1, df2, df3)

df <- df %>% dplyr::filter(
  geo %in% c(
    "European Union - 27 countries (from 2020)",
    "Belgium",
    "Bulgaria",
    "Denmark",
    "Germany (until 1990 former territory of the FRG)",
    "Estonia",
    "Ireland",
    "Greece",
    "Spain",
    "France",
    "Croatia",
    "Italy",
    "Netherlands",
    "Austria",
    "Poland",
    "Portugal",
    "Finland",
    "Sweden",
    "Norway",
    "Switzerland"
  )
)
df$geo[df$geo == "European Union - 27 countries (from 2020)"] <- "EU-27"
df$geo[df$geo == "Germany (until 1990 former territory of the FRG)"] <- "Germany"

df_long <- pivot_longer(df, total:below)


df_long$value <- as.numeric(df_long$value)
df_long$geo <- reorder(df_long$geo, df_long$value)

ggplot(df_long, aes(x = geo, y = value, fill = name)) +
  geom_bar(position = "dodge", stat = "identity") +
  xlab("") +
  ylab("Tenants in Rented Property \n at Market or Reduced Price (in % of Population)") +
  coord_flip() +
  scale_fill_manual(
    labels = c("Above Poverty Threshold", "Below Poverty Threshold", "Total"),
    values = c("antiquewhite4", "darkgoldenrod1", "steelblue4")
  ) +
  theme(legend.title = element_blank(), text = element_text(size = 16))

ggsave("fig/share_tenant.pdf")
